Couchbase (and many other document databases) use JSON objects for data. JSON is a powerful, human readable format to store data. When comparing to data types in relational tables, there are some similarities, and there are some important differences.

All JSON data is made up of 6 types: string, number, boolean, array, object, and null. There are a lot of data types available in SQL Server. Let’s start with a table that is a kind of "literal" translation, and work from there.

It’s important to understand how JSON works. I’ve listed some high-level differences between JSON data types and SQL Server data types. Assuming you already understand SQL data types, you might want to spend some time learning more about JSON and JSON data types.

A string in SQL Server is often defined by a length. nvarchar(50) or nvarchar(MAX) for instance. In JSON, you don’t need to define a length. Just use a string.

A number in SQL Server varies widely based on what you are using it for. The number type in JSON is flexible, in that it can store integers, decimal, or floating point. In specialized circumstances, like if you need a specific precision or you need to store very large numbers, you may want to store a number as a string instead.

A boolean in JSON is true/false. In SQL Server, it’s roughly equivalent: a bit that represents true/false.

In JSON, any value can be null. In SQL Server, you set this on a field-by-field basis. If a field in SQL Server is not set to "nullable", then it will be enforced. In a JSON document, there is no such enforcement.

JSON has no date data type. Often dates are stored as UNIX timestamps, but you could also use string representations or other formats for dates. The N1QL query language has a variety of date functions available, so if you want to use N1QL on dates, you can use those functions to plan your date storage accordingly.

In SQL Server, there is a geography data type. In Couchbase, the GeoJSON format is supported.

There are some other specialized data types in SQL Server, including hierarchyid, and xml. Typically, these would be unrolled in JSON objects and/or referenced by key (as explored in part 1 of this blog series on data modeling). You can still store XML/JSON within a string if you want, but if you do, then you can’t use the full power of N1QL on those fields.

Depending on your organization and your team, you may have to bring in people from multiple roles to ensure a successful migration. If you have a DBA, that DBA will have to know how to run and manage Couchbase just as well as SQL Server. If you are DevOps, or have a DevOps team, it’s important to involve them early on, so that they are aware of what you’re doing and can help you coordinate your efforts. Moving to a document database does not mean that you no longer need DBAs or Ops or DevOps to be involved. These roles should also be involved when doing data modeling, if possible, so that they can provide input and understand what is going on.

After you’ve designed your model with part 1 on data modeling, you can start moving data over to Couchbase.

For a naive migration (1 row to 1 document), you can write a very simple program to loop through the tables, columns, and values of a relational database and spit out corresponding documents. A tool like Dapper would handle all the data type translations within C# and feed them into the Couchbase .NET SDK.

Completely flat data is relatively uncommon, however, so for more complex models, you will probably need to write code to migrate from the old relational model to the new document model.

Here are some things you want to keep in mind when writing migration code (of any kind, but especially relational-to-nonrelational):

public class ShoppingCartMigrator
{
    readonly IBucket _bucket;
    readonly SqlToCbContext _context;

    public ShoppingCartMigrator(IBucket bucket, SqlToCbContext context)
    {
        _bucket = bucket;
        _context = context;
    }
}

public bool Go()
{
    var carts = _context.ShoppingCarts
        .Include(x => x.Items)
        .ToList();
    foreach (var cart in carts)
    {
        var cartDocument = new Document<dynamic>
        {
            Id = cart.Id.ToString(),
            Content = MapCart(cart)
        };
        var result = _bucket.Insert(cartDocument);
        if (!result.Success)
        {
            Console.WriteLine($"There was an error migrating Shopping Cart {cart.Id}");
            return false;
        }
        Console.WriteLine($"Successfully migrated Shopping Cart {cart.Id}");
    }
    return true;
}

public void Cleanup()
{
    Console.WriteLine("Delete all shopping carts...");
    var result = _bucket.Query<dynamic>("DELETE FROM `sqltocb` WHERE type='ShoppingCart';");
    if (!result.Success)
    {
        Console.WriteLine($"{result.Exception?.Message}");
        Console.WriteLine($"{result.Message}");
    }
}

Not everything in SQL Server has a direct counterpart in Couchbase. In some cases, it won’t ever have a counterpart. In some cases, there will be a rough equivalent. Some features will arrive in the future, as Couchbase is under fast-paced, active, open-source development, and new features are being added when appropriate.

Also keep in mind that document databases and NoSQL databases often force business logic out of the database to a larger extent than relational databases. As nice as it would be if Couchbase Server had every feature under the sun, there are always tradeoffs. Some are technical in nature, some are product design decisions. Tradeoffs could be made to add relational-style features, but at some point in that journey, Couchbase stops being a fast, scalable database and starts being "just another" relational database. There is certainly a lot of convergence in both relational and non-relational databases, and a lot of change happening every year.

With that in mind, stay tuned for the final blog post in the series. This will cover the changes to application coding that come with using Couchbase, including:

This blog post compared and contrasted the data features available in Couchbase Server with SQL Server. If you are currently using SQL Server and are considering adding a document database to your project or starting a new project, I am here to help.

Check out the Couchbase developer portal for more details.

Please contact me at matthew.groves@couchbase.com, ask a question on the Couchbase Forums, or ping me on Twitter @mgroves.

Before we get started, I want to spend a little bit of time on motivation. There are 3 main reasons why one might consider using a document data store instead of (or in addition to) a relational database. Your motivation may be one or all three:

For these reasons and others, Gannett switched from SQL Server to Couchbase Server. If you are considering this, definitely check out Gannett’s full presentation.

It should be noted that document databases and relational databases can be complimentary. Your application may be best served by one, the other, or a combination of both. In many cases, it simply is not possible to completely remove relational databases from your design, but a document database like Couchbase Server can still bring the above benefits to your software. The rest of this blog series will assume you have a SQL Server background and are either replacing, supplimenting, or starting a new greenfield project using Couchbase.

The ease or difficulty of transitioning an existing application varies widely based on a number of factors. In some cases it may be extremely easy; in some cases it will be time-consuming and difficult; in some (shrinking number of) cases it may not even be a good idea.

The first step is to understand how data is modeled in a document database. In a relational database, data is typically stored flat in a table and it is given structure with primary and foreign keys. As a simple example, let’s consider a relational database for a web site that has a shopping cart as well as social media features. (In this example, those features are unrelated to keep things simple).

In a document database, data is stored as keys and values. A Couchbase bucket contains documents; each document has a unique key and a JSON value. There are no foreign keys (or, more accurately, there are no foreign key constraints).

Here’s a high-level comparison of SQL Server features/naming as compared to Couchbase:

These comparisons are a metaphorical starting point. Looking at that table, it might be tempting to take a simplistic approach. "I have 5 tables, therefore I’ll just create 5 different types of documents, with one document per row." This is the equivalent of literally translating a written language. The approach may work sometimes, but it doesn’t take into account the full power of a document database that uses JSON. Just as a literal translation of a written language doesn’t take into account cultural context, idioms, and historical context.

Because of the flexibility of JSON, the data in a document database can be structured more like a domain object in your application. Therefore you don’t have an impedence mismatch that is often addressed by OR/M tools like Entity Framework and NHibernate.

There are two main approaches you can use when modeling data in Couchbase that we will examine further:

Let’s consider the "shopping cart" entity.

To represent this in a relational database would likely require two tables: a ShoppingCart table and a ShoppingCartItem table with a foreign key to a row in ShoppingCart.

When creating the model for a document database, the decision has to be made whether to continue modeling this as two separate entities (e.g. a Shopping Cart document and corresponding Shopping Cart Item documents) or whether to "denormalize" and combine a row from ShoppingCart and row(s) from ShoppingCartItem into a single document to represent a shopping cart.

In Couchbase, using a denormalization strategy, a shopping cart and the items in it would be represented by a single document.

{
  "user": "mgroves",
  "dateCreated": "2017-02-02T15:28:11.0208157-05:00",
  "items": [
    {
      "name": "BB-8 Sphero",
      "price": 80.18,
      "quantity": 1
    },
    {
      "name": "Shopkins Season 5",
      "price": 59.99,
      "quantity": 2
    }
  ],
  "type": "ShoppingCart"
}

Notice that the relationship between the items and the shopping cart is now implicit to being contained in the same document. No more need for an ID on the items to represent a relationship.

In C#, you would likely define ShoppingCart and Item classes to model this data:

public class ShoppingCart
{
    public Guid Id { get; set; }
    public string User { get; set; }
    public DateTime DateCreated { get; set; }
    public List<Item> Items { get; set; }
}

public class Item
{
    public Guid Id { get; set; }    // necessary for SQL Server, not for Couchbase
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

These classes would still make sense with Couchbase, so you can reuse them or design them this way. But with a relational database, this design does not match up in a direct way.

Hence the need for OR/Ms like NHibernate or Entity Framework. The way the above model can be mapped to a relational database is represented in Entity Framework* like this:

public class ShoppingCartMap : EntityTypeConfiguration<ShoppingCart>
{
    public ShoppingCartMap()
    {
        this.HasKey(m => m.Id);

        this.ToTable("ShoppingCart");
        this.Property(m => m.User);
        this.Property(m => m.DateCreated);
        this.HasMany(m => m.Items)
            .WithOptional()
            .HasForeignKey(m => m.ShoppingCartId);
    }
}

public class ShoppingCartItemMap : EntityTypeConfiguration<Item>
{
    public ShoppingCartItemMap()
    {
        this.HasKey(m => m.Id);

        this.ToTable("ShoppingCartItems");
        this.Property(m => m.Name);
        this.Property(m => m.Price);
        this.Property(m => m.Quantity);
    }
}

*Other OR/Ms will have similar mappings

Based on these mappings and an analysis of the use cases, I could decide that it would be modeled as a single document in Couchbase. ShoppingCartItemMap only exists so that the OR/M knows how to populate the Items property in ShoppingCart. Also, it’s unlikely that the application will be doing reads of the shopping cart without also needing to read the items.

In a later post, OR/Ms will be discussed further, but for now I can say that the ShoppingCartMap and ShoppingCartItemMap classes are not necessary when using Couchbase, and the Id field from Item isn’t necessary. In fact, the Couchbase .NET SDK can directly populate a ShoppingCart object without an OR/M in a single line of code:

public ShoppingCart GetCartById(Guid id)
{
    return _bucket.Get<ShoppingCart>(id.ToString()).Value;
}

This isn’t to say that using Couchbase will always result in shorter, easier to read code. But for certain use cases, it can definitely have an impact.

It’s not always possible or optimal to denormalize relationships like the ShoppingCart example. In many cases, a document will need to reference another document. Depending on how your application expects to do reads and writes, you may want to keep your model in separate documents by using referencing.

Let’s look at an example where referencing might be the best approach. Suppose your application has some social media elements. Users can have friends, and users can post text updates.

One way to model this:

With two users, two updates, we would have 4 documents in Couchbase that look like this:

[
  // Key: "7fc5503f-2092-4bac-8c33-65ef5b388f4b"
  {
    "friends": [
      "c5f05561-9fbf-4ab0-b68f-e392267c0703"
    ],
    "name": "Matt Groves",
    "type": "User"
  },

  // Key: "c5f05561-9fbf-4ab0-b68f-e392267c0703"
  {
    "friends": [ ],
    "name": "Nic Raboy",
    "type": "User"
  },

  // Key: "5262cf62-eb10-4fdd-87ca-716321405663"
  {
    "body": "Nostrum eligendi aspernatur enim repellat culpa.",
    "postedDate": "2017-02-02T16:19:45.2792288-05:00",
    "type": "Update",
    "user": "7fc5503f-2092-4bac-8c33-65ef5b388f4b"
  },

  // Key: "8d710b83-a830-4267-991e-4654671eb14f"
  {
    "body": "Autem occaecati quam vel. In aspernatur dolorum.",
    "postedDate": "2017-02-02T16:19:48.7812386-05:00",
    "type": "Update",
    "user": "c5f05561-9fbf-4ab0-b68f-e392267c0703"
  }
]

I decided to model 'friends' as a one-way relationship (like Twitter) for this example, which is why Matt Groves has Nic Raboy as a friend but not vice-versa. (Don’t read too much into this, Nic :).

The way to model this in C# could be:

public class FriendbookUser
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual List<FriendbookUser> Friends { get; set; }
}

public class Update
{
    public Guid Id { get; set; }
    public DateTime PostedDate { get; set; }
    public string Body { get; set; }

    public virtual FriendbookUser User { get; set; }
    public Guid UserId { get; set; }
}

The Update to FriendbookUser relationship can be modeled as either a Guid or as another FriendbookUser object. This is an implementation detail. You might prefer one, the other, or both, depending on your application needs and/or how your OR/M works. In either case, the underlying model is the same.

Here’s the mapping I used for these classes in Entity Framework. Your mileage may vary, depending on how you use EF or other OR/M tools. Focus on the underlying model and not the details of the OR/M mapping tool.

public class UpdateMap : EntityTypeConfiguration<Update>
{
    public UpdateMap()
    {
        this.HasKey(m => m.Id);

        this.ToTable("FriendBookUpdates");
        this.Property(m => m.Body);
        this.Property(m => m.PostedDate);
        this.HasRequired(m => m.User)
            .WithMany()
            .HasForeignKey(m => m.UserId);
    }
}

public class FriendbookUserMap : EntityTypeConfiguration<FriendbookUser>
{
    public FriendbookUserMap()
    {
        this.HasKey(m => m.Id);

        this.ToTable("FriendBookUsers");
        this.Property(m => m.Name);
        this.HasMany(t => t.Friends)
            .WithMany()
            .Map(m =>
            {
                m.MapLeftKey("UserId");
                m.MapRightKey("FriendUserId");
                m.ToTable("FriendBookUsersFriends");
            });
    }
}

If, instead of storing these entities as separate documents, we applied the same denormalization as the shopping cart example and attempted to store a user and updates in one document, we would end up with some problems.

Note: There’s an N+1 problem here too (friends of friends, etc), but I’m not going to spend time on addressing that. It’s a problem that’s not unique to either database.

Additionally, it may not be the case that when the application reads or writes a user that it will need to read or write friends & updates. And, when writing an update, it’s not likely that the application will need to update a user. Since these entities may often be read/written on their own, that indicates that they need to be modeled as separate documents.

Note the array in the Friends field in the user document and the value in the User field in the update document. These values can be used to retrieve the associated documents. Later in this post, I’ll discuss how to do it with key/value operations and how to do it with N1QL.

To sum up, there are two ways to model data in a document database. The shopping cart example used nested objects, while the social media example used separate documents. In those examples, it was relatively straightforward to choose. When you’re making your own modeling decisions, here’s a handy cheat sheet:

public List<Update> GetTenLatestUpdates()
{
    var n1ql = @"SELECT up.body, up.postedDate, { 'id': META(u).id, u.name} AS `user`
        FROM `sqltocb` up
        JOIN `sqltocb` u ON KEYS up.`user`
        WHERE up.type = 'Update'
        ORDER BY STR_TO_MILLIS(up.postedDate) DESC
        LIMIT 10;";
    var query = QueryRequest.Create(n1ql);
    query.ScanConsistency(ScanConsistency.RequestPlus);
    var result = _bucket.Query<Update>(query);
    return result.Rows;
}

[
  {
    "body": "Autem occaecati quam vel. In aspernatur dolorum.",
    "postedDate": "2017-02-02T16:19:48.7812386-05:00",
    "user": {
      "id": "c5f05561-9fbf-4ab0-b68f-e392267c0703",
      "name": "Bob Johnson"
    }
  },
  {
    "body": "Nostrum eligendi aspernatur enim repellat culpa eligendi maiores et.",
    "postedDate": "2017-02-02T16:19:45.2792288-05:00",
    "user": {
      "id": "7fc5503f-2092-4bac-8c33-65ef5b388f4b",
      "name": "Steve Oberbrunner"
    }
  },

  // ... etc ...
]

CREATE INDEX IX_Name ON `SocialMedia` (Name) USING GSI;

In relational databases, rows of data (typically, not always) correspond to a primary key, which is often an integer or a Guid, and sometimes a composite key. These keys don’t necessarily have any meaning: they are just used to identify a row within a table. For instance, two rows of data in two different tables may have the same key (an integer value of 123, for instance), but that doesn’t necessarily mean the data is related. This is because the schema enforced by relational databases often conveys meaning on its own (e.g. a table name).

In document databases like Couchbase, there isn’t anything equivalent to a table, per se. Each document in a bucket must have a unique key. But a bucket can have a variety of documents in it. Therefore, it’s often wise to come up with a way to differentiate documents within a bucket.

{
    "address" : "1800 Brown Rd",
    "city" : "Groveport",
    "state" : "OH",
    "type" : "address"
}

SELECT d.*
FROM `default` d
WHERE d.type = 'address'

{
    "address" : "1800 Brown Rd",
    "city" : "Groveport",
    "state" : "OH",
    "documentInfo" : {
        "type" : "address",
        "lastUpdated" : "1/29/2017 1:31:10 PM",
        "lastUpdatedBy" : "mgroves"
    }
}

This blog post covered data modeling using denormalization, data modeling using referencing, key design, and discriminating fields. Modeling data in a document database is a thought process, something of an art form, and not a mechanical process. There is no prescription on how to model your data in a document database: it depends greatly on how your application interacts with your data.

You can get the source code for the entire blog series on GitHub now, parts of which were featured in this blog post. If you have questions about various parts of that code, feel free to leave a comment below, or open an issue on GitHub.

Stay tuned for the next blog in the series, where data and data migration will be discussed.

If you have any questions, please leave a comment below, contact me on Twitter, or use the Couchbase Forums.